Trabalho Final - CED810¶
Autores: Alejandro, Fernando Scherer, Glauco, Joao Loose, Luciano Ventura, Vitor Gioia
Atividades a fazer:
Carregamento dos dados
Carga de dados do arquivo para o ambiente de trabalho.
Exploração dos Dados
Entender o conjunto de dado fornecido, executando:Testes estatísticos de correlação das variáveis em relação à variável alvo.Testes de hipóteses (t-value, e chi-squared).Sumarizações; eVisualizações gráficas de distribuição de cada variável.
Transformar dos Dados e Preparar o conjunto de dados para a tarefa de análise
Limpar, transformar e selecionar os dados relevantes para o contexto da análise.De acordo com os dados e o requisito de análise escolher o tipo de tarefa a ser executada na análise.Preparar o dataset para se adequar ao algoritmo de análise selecionado para atender ao requisito especificado.Durante a construção do conjunto de dados, novas informações podem ser criadas.
Aplicar a tarefa de mineração
Execução do algoritmo de análise escolhido para entregar a resposta esperada.Geração de resultados (métricas, plotagens ou visualizações) próprios do algoritmo. Mostre a figura do resultado se houver.Análise dos resultados e Explicação dos resultados.
Apresentar (descrever) o perfil de clientes com alta probabilidade de compra de seguro imobiliário.
Resumo¶
Este arquivo contém a resolução do trabalho final do curso CEDS-810: Análise em Big Data. Neste trabalho, os autores aplicam algumas técnicas e modelos a fim de saber o perfil de clientes que têm alta probabilidade de comprar seguro imobiliário.
Sumário¶
Preparação do Ambiente¶
In [ ]:
!apt-get update
!apt-get install openjdk-8-jdk-headless -qq > /dev/null
!wget -q http://archive.apache.org/dist/spark/spark-3.1.1/spark-3.1.1-bin-hadoop3.2.tgz
!tar xf spark-3.1.1-bin-hadoop3.2.tgz
!pip install -q findspark
Get:1 http://security.ubuntu.com/ubuntu jammy-security InRelease [110 kB] Hit:2 http://archive.ubuntu.com/ubuntu jammy InRelease Get:3 http://archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Get:4 http://archive.ubuntu.com/ubuntu jammy-backports InRelease [109 kB] Get:5 https://cloud.r-project.org/bin/linux/ubuntu jammy-cran40/ InRelease [3,626 B] Hit:6 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu2204/x86_64 InRelease Hit:7 https://ppa.launchpadcontent.net/c2d4u.team/c2d4u4.0+/ubuntu jammy InRelease Get:8 https://ppa.launchpadcontent.net/deadsnakes/ppa/ubuntu jammy InRelease [18.1 kB] Get:9 http://archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [1,370 kB] Get:10 https://ppa.launchpadcontent.net/graphics-drivers/ppa/ubuntu jammy InRelease [24.3 kB] Get:11 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [2,035 kB] Hit:12 https://ppa.launchpadcontent.net/ubuntugis/ppa/ubuntu jammy InRelease Get:13 http://security.ubuntu.com/ubuntu jammy-security/universe amd64 Packages [1,077 kB] Get:14 http://security.ubuntu.com/ubuntu jammy-security/main amd64 Packages [1,755 kB] Get:15 https://cloud.r-project.org/bin/linux/ubuntu jammy-cran40/ Packages [52.9 kB] Get:16 https://ppa.launchpadcontent.net/deadsnakes/ppa/ubuntu jammy/main amd64 Packages [28.3 kB] Get:17 https://ppa.launchpadcontent.net/graphics-drivers/ppa/ubuntu jammy/main amd64 Packages [44.2 kB] Fetched 6,747 kB in 4s (1,876 kB/s) Reading package lists... Done
In [ ]:
import os
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
os.environ["SPARK_HOME"] = "/content/spark-3.1.1-bin-hadoop3.2"
import findspark
findspark.init()
In [ ]:
!ls
customer.csv sample_data spark-3.1.1-bin-hadoop3.2 spark-3.1.1-bin-hadoop3.2.tgz
In [ ]:
from pyspark.sql import SparkSession
spark = SparkSession.builder.master("local[*]").getOrCreate()
spark
Out[ ]:
SparkSession - hive
In [ ]:
# Carregando bibliotecas que serão utilizadas na atividade.
from pyspark.sql import SparkSession
from pyspark.sql.functions import regexp_replace, col, avg, count, format_number, rand, stddev
from pyspark.ml.feature import VectorAssembler, StandardScaler
import seaborn as sns
import matplotlib.pyplot as plt
import pandas as pd
import math
from pyspark.ml.clustering import KMeans
from pyspark.ml.evaluation import ClusteringEvaluator
from numpy import mean, log
from pyspark.sql.types import IntegerType, DoubleType
from pyspark.sql.functions import when
from scipy.stats import t
from pyspark.ml.feature import StringIndexer
from pyspark.ml.stat import ChiSquareTest
from pyspark.ml.linalg import Vectors, VectorUDT
from pyspark.sql import functions as F
from pyspark.ml.feature import OneHotEncoder
from pyspark.ml import Pipeline
In [ ]:
# Iniciar SparkSession com suporte ao Hive.
spark = SparkSession.builder.appName("Acesso Hive").enableHiveSupport().getOrCreate()
In [ ]:
df = spark.read.csv('customer.csv', inferSchema=True, header=True, sep=";")
df = df.withColumn("MONEY_MONTLY_OVERDRAWN", regexp_replace(col("MONEY_MONTLY_OVERDRAWN"), ",", ".").cast("float"))
df = df.withColumn("LTV", regexp_replace(col("LTV"), ",", ".").cast("float"))
Exploração dos Dados¶
In [ ]:
df.printSchema()
root |-- CUSTOMER_ID: string (nullable = true) |-- LAST: string (nullable = true) |-- FIRST: string (nullable = true) |-- STATE: string (nullable = true) |-- REGION: string (nullable = true) |-- SEX: string (nullable = true) |-- PROFESSION: string (nullable = true) |-- BUY_INSURANCE: string (nullable = true) |-- AGE: integer (nullable = true) |-- HAS_CHILDREN: integer (nullable = true) |-- SALARY: integer (nullable = true) |-- N_OF_DEPENDENTS: integer (nullable = true) |-- CAR_OWNERSHIP: integer (nullable = true) |-- HOUSE_OWNERSHIP: integer (nullable = true) |-- TIME_AS_CUSTOMER: integer (nullable = true) |-- MARITAL_STATUS: string (nullable = true) |-- CREDIT_BALANCE: integer (nullable = true) |-- BANK_FUNDS: integer (nullable = true) |-- CHECKING_AMOUNT: integer (nullable = true) |-- MONEY_MONTLY_OVERDRAWN: float (nullable = true) |-- T_AMOUNT_AUTOM_PAYMENTS: integer (nullable = true) |-- MONTHLY_CHECKS_WRITTEN: integer (nullable = true) |-- MORTGAGE_AMOUNT: integer (nullable = true) |-- N_TRANS_ATM: integer (nullable = true) |-- N_MORTGAGES: integer (nullable = true) |-- N_TRANS_TELLER: integer (nullable = true) |-- CREDIT_CARD_LIMITS: integer (nullable = true) |-- N_TRANS_KIOSK: integer (nullable = true) |-- N_TRANS_WEB_BANK: integer (nullable = true) |-- LTV: float (nullable = true) |-- LTV_BIN: string (nullable = true)
In [ ]:
numeric_columns = ['AGE', 'SALARY', 'TIME_AS_CUSTOMER', 'CREDIT_BALANCE','CAR_OWNERSHIP', 'HOUSE_OWNERSHIP',
'BANK_FUNDS', 'CHECKING_AMOUNT','MONEY_MONTLY_OVERDRAWN','T_AMOUNT_AUTOM_PAYMENTS',
'MONTHLY_CHECKS_WRITTEN', 'MORTGAGE_AMOUNT','N_TRANS_ATM','N_MORTGAGES', 'N_TRANS_TELLER',
'CREDIT_CARD_LIMITS','N_TRANS_KIOSK', 'N_TRANS_WEB_BANK', 'LTV']
categorical_columns = ['HAS_CHILDREN', 'MARITAL_STATUS', 'REGION', 'SEX', 'PROFESSION', 'LTV_BIN']
Summary dos dados¶
In [ ]:
show_batch = 5
range_batch = math.ceil(len(numeric_columns)/show_batch)
base_idx = 0
for _ in range(range_batch):
df.select(numeric_columns[base_idx:base_idx+show_batch]).summary().show()
base_idx += show_batch
+-------+------------------+-----------------+------------------+------------------+------------------+ |summary| AGE| SALARY| TIME_AS_CUSTOMER| CREDIT_BALANCE| CAR_OWNERSHIP| +-------+------------------+-----------------+------------------+------------------+------------------+ | count| 1015| 1015| 1015| 1015| 1015| | mean|38.190147783251234|65102.80295566502|2.4285714285714284|2234.1014778325125|0.9467980295566503| | stddev|14.918394106597274|6848.442846277457|1.2333992047170599|11727.873803601524|0.2245466473592848| | min| 0| 37572| 1| 0| 0| | 25%| 27| 60786| 1| 0| 1| | 50%| 36| 64173| 2| 0| 1| | 75%| 48| 68407| 3| 0| 1| | max| 84| 109943| 5| 170498| 1| +-------+------------------+-----------------+------------------+------------------+------------------+ +-------+------------------+-----------------+------------------+----------------------+-----------------------+ |summary| HOUSE_OWNERSHIP| BANK_FUNDS| CHECKING_AMOUNT|MONEY_MONTLY_OVERDRAWN|T_AMOUNT_AUTOM_PAYMENTS| +-------+------------------+-----------------+------------------+----------------------+-----------------------+ | count| 1015| 1015| 1015| 1015| 1015| | mean|0.8049261083743843|2639.839408866995| 1055.848275862069| 53.708157882314595| 4980.337931034483| | stddev|0.5077072350739917|4996.100695402684|3124.4059848269044| 1.6850125888497685| 20443.6578124019| | min| 0| 0| 25| 32.16| 0| | 25%| 1| 0| 25| 53.06| 188| | 50%| 1| 500| 25| 53.24| 623| | 75%| 1| 2900| 229| 53.82| 2324| | max| 2| 36000| 23476| 73.61| 499362| +-------+------------------+-----------------+------------------+----------------------+-----------------------+ +-------+----------------------+------------------+------------------+------------------+------------------+ |summary|MONTHLY_CHECKS_WRITTEN| MORTGAGE_AMOUNT| N_TRANS_ATM| N_MORTGAGES| N_TRANS_TELLER| +-------+----------------------+------------------+------------------+------------------+------------------+ | count| 1015| 1015| 1015| 1015| 1015| | mean| 4.311330049261084|2066.1221674876847| 2.826600985221675|0.8049261083743843|1.7310344827586206| | stddev| 4.817335938750649| 3184.931347333881|1.8914011204972785|0.5077072350739917|1.4993662085725994| | min| 0| 0| 0| 0| 0| | 25%| 1| 176| 1| 1| 1| | 50%| 3| 1100| 3| 1| 1| | 75%| 5| 3000| 4| 1| 3| | max| 18| 45000| 8| 2| 9| +-------+----------------------+------------------+------------------+------------------+------------------+ +-------+------------------+------------------+------------------+-----------------+ |summary|CREDIT_CARD_LIMITS| N_TRANS_KIOSK| N_TRANS_WEB_BANK| LTV| +-------+------------------+------------------+------------------+-----------------+ | count| 1015| 1015| 1015| 1015| | mean| 1285.615763546798|1.8640394088669952|1449.6758620689654|22452.39039408867| | stddev| 858.2066930270321|1.8260760353304921|2428.9208272335786|6579.356570077857| | min| 500| 0| 0| 0.0| | 25%| 800| 1| 250| 18928.5| | 50%| 1000| 1| 800| 23132.0| | 75%| 1500| 3| 2000| 26336.0| | max| 5000| 10| 45000| 43101.25| +-------+------------------+------------------+------------------+-----------------+
Distribuição de cada Variável¶
Variáveis Numéricas¶
In [ ]:
X = df.toPandas()
X_plot = X[['AGE', 'SALARY', 'TIME_AS_CUSTOMER', 'CREDIT_BALANCE']]
plt.figure(figsize=(12, 8))
for i, column in enumerate(X_plot.columns, 1):
plt.subplot(2, 2, i)
sns.violinplot(y=X_plot[column])
plt.title(column)
plt.tight_layout()
plt.show()
X_plot = X[['CAR_OWNERSHIP', 'HOUSE_OWNERSHIP','BANK_FUNDS', 'CHECKING_AMOUNT']]
plt.figure(figsize=(12, 8))
for i, column in enumerate(X_plot.columns, 1):
plt.subplot(2, 2, i)
sns.violinplot(y=X_plot[column])
plt.title(column)
plt.tight_layout()
plt.show()
X_plot = X[['MONEY_MONTLY_OVERDRAWN','T_AMOUNT_AUTOM_PAYMENTS','MONTHLY_CHECKS_WRITTEN', 'MORTGAGE_AMOUNT']]
plt.figure(figsize=(12, 8))
for i, column in enumerate(X_plot.columns, 1):
plt.subplot(2, 2, i)
sns.violinplot(y=X_plot[column])
plt.title(column)
plt.tight_layout()
plt.show()
X_plot = X[['N_TRANS_ATM','N_MORTGAGES', 'N_TRANS_TELLER', 'CREDIT_CARD_LIMITS']]
plt.figure(figsize=(12, 8))
for i, column in enumerate(X_plot.columns, 1):
plt.subplot(2, 2, i)
sns.violinplot(y=X_plot[column])
plt.title(column)
plt.tight_layout()
plt.show()
X_plot = X[['N_TRANS_KIOSK', 'N_TRANS_WEB_BANK', 'LTV']]
plt.figure(figsize=(12, 8))
for i, column in enumerate(X_plot.columns, 1):
plt.subplot(2, 2, i)
sns.violinplot(y=X_plot[column])
plt.title(column)
plt.tight_layout()
plt.show()
Variáveis Categóricas¶
In [ ]:
num_rows = 2
num_cols = 3
fig, axes = plt.subplots(num_rows, num_cols, figsize=(15, 10))
for i, column in enumerate(categorical_columns):
row_index = i // num_cols
col_index = i % num_cols
category_counts = df.groupBy(column).agg(count("*").alias("Count")).orderBy(column)
category_counts = category_counts.limit(10) # top 10 categories
categories = category_counts.select(column).rdd.flatMap(lambda x: x).collect()
counts = category_counts.select("Count").rdd.flatMap(lambda x: x).collect()
ax = axes[row_index, col_index]
ax.bar(categories, counts)
ax.set_xlabel("Category")
ax.set_ylabel("Count")
ax.set_title(f'Bar plot of {column}')
ax.tick_params(axis='x', rotation=90)
plt.tight_layout()
plt.show()
Correlação das Variáveis¶
Heatmap¶
In [ ]:
df_num = df.withColumn("BUY_INSURANCE", when(df["BUY_INSURANCE"] == 'No', 0).otherwise(1))
new_numeric_columns = ["BUY_INSURANCE"] + numeric_columns
correlation_matrix = df_num.select(new_numeric_columns).toPandas().corr()
plt.figure(figsize=(15, 15))
sns.heatmap(correlation_matrix, annot=True, cmap="coolwarm", fmt=".3f")
plt.title("Correlation Heatmap")
plt.show()
P-valor¶
In [ ]:
def calculate_stats (df, numeric_columns, target_col):
df_return = [expr for c in numeric_columns for expr in (avg(c).alias(f"mean_{c}"), stddev(c).alias(f"stddev_{c}"), count(c).alias(f"count_{c}"))]
return df.groupBy(target_col).agg(*df_return)
stats = calculate_stats(df, numeric_columns, 'BUY_INSURANCE')
def t_test_and_p_value(rowA, rowB, numeric_columns):
t_stats = {}
p_values = {}
for col in numeric_columns:
meanA = rowA[f'mean_{col}']
meanB = rowB[f'mean_{col}']
stdA = rowA[f'stddev_{col}']
stdB = rowB[f'stddev_{col}']
nA = rowA[f'count_{col}']
nB = rowB[f'count_{col}']
if nA > 0 and nB > 0 and (stdA > 0 or stdB > 0): # adicionando condição para desvios padrão
denominador = math.sqrt((stdA**2)/nA + (stdB**2)/nB)
if denominador != 0:
t_statistic = (meanA - meanB) / denominador
degrees_of_freedom = nA + nB - 2
# Calculando p-value usando a CDF da distribuição t de Student
p_value = 2 * t.sf(math.fabs(t_statistic), df=degrees_of_freedom)
t_stats[col] = t_statistic
p_values[col] = p_value
else:
t_stats[col] = None
p_values[col] = None
else:
t_stats[col] = None
p_values[col] = None
return t_stats, p_values
# Coletando estatísticas e calculando t-test e p-values
stats_A = stats.filter(df.BUY_INSURANCE == 'Yes').collect()[0]
stats_B = stats.filter(df.BUY_INSURANCE == 'No').collect()[0]
t_stats, p_values = t_test_and_p_value(stats_A, stats_B, numeric_columns)
In [ ]:
alpha = 0.05
print("Features that indicate good prediction of target - FEATURE: p-value")
for key in p_values.keys():
if p_values[key] <= 0.05:
print(f'{key}: {p_values[key]:.4f}')
Features that indicate good prediction of target - FEATURE: p-value CREDIT_BALANCE: 0.0260 CAR_OWNERSHIP: 0.0001 HOUSE_OWNERSHIP: 0.0000 BANK_FUNDS: 0.0000 CHECKING_AMOUNT: 0.0001 MONEY_MONTLY_OVERDRAWN: 0.0000 MONTHLY_CHECKS_WRITTEN: 0.0000 MORTGAGE_AMOUNT: 0.0040 N_TRANS_ATM: 0.0000 N_MORTGAGES: 0.0000 N_TRANS_TELLER: 0.0000
Testes de hipóteses¶
In [ ]:
target_col = "BUY_INSURANCE"
df_num_target = df.withColumn(target_col, when(df[target_col] == "Yes","1") \
.when(df[target_col] == "No","0"))
df_num_target = df_num_target.withColumn(target_col, df_num_target[target_col].cast(DoubleType()))
rows = df_num_target.collect()
# Transform each Row object into a tuple
df_dict_list = [row.asDict() for row in rows]
print("Chi-squared test results:")
for ctg_col in categorical_columns:
chi_tuple = [(el[ctg_col], el[target_col]) for el in df_dict_list]
chi_df = spark.createDataFrame(chi_tuple, [ctg_col, target_col])
# Index categorical variable
indexer = StringIndexer(inputCol=ctg_col, outputCol="category_index")
indexer_model = indexer.fit(chi_df)
df_indexed = indexer_model.transform(chi_df)
# Convert category_index column to VectorUDT format
to_vector_udf = F.udf(lambda x: Vectors.dense(x), VectorUDT())
df_indexed = df_indexed.withColumn("category_index", to_vector_udf(col("category_index")))
# Perform Chi-squared test
chi_test_result = ChiSquareTest.test(df_indexed, "category_index", target_col)
print(ctg_col)
chi_test_result.show(truncate=False)
Chi-squared test results: HAS_CHILDREN +----------------------+----------------+-------------------+ |pValues |degreesOfFreedom|statistics | +----------------------+----------------+-------------------+ |[0.005992457129742501]|[1] |[7.552569350495127]| +----------------------+----------------+-------------------+ MARITAL_STATUS +-----------------------+----------------+-------------------+ |pValues |degreesOfFreedom|statistics | +-----------------------+----------------+-------------------+ |[1.9149767013781904E-5]|[4] |[27.08026520113879]| +-----------------------+----------------+-------------------+ REGION +---------------------+----------------+-------------------+ |pValues |degreesOfFreedom|statistics | +---------------------+----------------+-------------------+ |[0.05217332544528697]|[4] |[9.384595889693284]| +---------------------+----------------+-------------------+ SEX +-----------------------+----------------+-------------------+ |pValues |degreesOfFreedom|statistics | +-----------------------+----------------+-------------------+ |[2.0584352444230625E-5]|[1] |[18.13444935588536]| +-----------------------+----------------+-------------------+ PROFESSION +----------------------+----------------+--------------------+ |pValues |degreesOfFreedom|statistics | +----------------------+----------------+--------------------+ |[4.380012248672571E-6]|[94] |[167.86362856731037]| +----------------------+----------------+--------------------+ LTV_BIN +---------------------+----------------+-------------------+ |pValues |degreesOfFreedom|statistics | +---------------------+----------------+-------------------+ |[0.21098255815289646]|[3] |[4.514702984149751]| +---------------------+----------------+-------------------+
Transformação e Preparação dos Dados¶
Lendo CSV e corrigindo erros de , no lugar de .
In [ ]:
df = spark.read.csv('customer.csv', inferSchema=True, header=True, sep=";")
df = df.withColumn("MONEY_MONTLY_OVERDRAWN", regexp_replace(col("MONEY_MONTLY_OVERDRAWN"), ",", ".").cast("float"))
df = df.withColumn("LTV", regexp_replace(col("LTV"), ",", ".").cast("float"))
Removendo colunas não relevantes
In [ ]:
columns_to_drop = ["CUSTOMER_ID","LAST","FIRST"]
df = df.drop(*columns_to_drop)
Para essa tarefa o grupo decidiu em escolher o modelo de Decision Tree, logo faremos tratamentos nos dados para atender o modelo:
Criando um label:
In [ ]:
label_stringIdx = StringIndexer(inputCol = 'BUY_INSURANCE', outputCol = 'label')
df = label_stringIdx.fit(df).transform(df)
df.show()
+-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+ |STATE| REGION|SEX| PROFESSION|BUY_INSURANCE|AGE|HAS_CHILDREN|SALARY|N_OF_DEPENDENTS|CAR_OWNERSHIP|HOUSE_OWNERSHIP|TIME_AS_CUSTOMER|MARITAL_STATUS|CREDIT_BALANCE|BANK_FUNDS|CHECKING_AMOUNT|MONEY_MONTLY_OVERDRAWN|T_AMOUNT_AUTOM_PAYMENTS|MONTHLY_CHECKS_WRITTEN|MORTGAGE_AMOUNT|N_TRANS_ATM|N_MORTGAGES|N_TRANS_TELLER|CREDIT_CARD_LIMITS|N_TRANS_KIOSK|N_TRANS_WEB_BANK| LTV| LTV_BIN|label| +-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+ | WI| Midwest| F| PROF-9| No| 49| 1| 68696| 1| 1| 1| 1| WIDOWED| 0| 16100| 25| 53.14| 1749| 4| 5500| 2| 1| 5| 800| 1| 3700| 25574.0| HIGH| 0.0| | CA| West| M| Nurse| No| 24| 0| 73850| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 0| 1500| 1| 0| 21862.5| MEDIUM| 0.0| | MI| Midwest| M|Programmer/Developer| No| 26| 1| 60249| 2| 1| 1| 2| MARRIED| 452| 500| 134| 53.42| 625| 4| 1036| 4| 1| 2| 1000| 4| 1036|19662.25| MEDIUM| 0.0| | NY|NorthEast| M|Programmer/Developer| No| 32| 0| 60466| 1| 1| 0| 1| SINGLE| 0| 650| 265| 53.18| 278| 17| 0| 3| 0| 2| 700| 3| 0| 16816.5| MEDIUM| 0.0| | NY|NorthEast| M|Construction Laborer| No| 24| 0| 76570| 3| 1| 1| 3| SINGLE| 0| 0| 25| 53.06| 0| 1| 358| 0| 1| 0| 1500| 1| 358| 27042.5| HIGH| 0.0| | UT|Southwest| M| Truck Driver| No| 35| 1| 62756| 1| 1| 1| 1| MARRIED| 0| 501| 1382| 53.32| 1636| 8| 1500| 3| 1| 2| 500| 1| 1500| 22689.0| HIGH| 0.0| | NY|NorthEast| F| IT Staff| No| 36| 1| 62886| 1| 1| 1| 1| MARRIED| 0| 600| 25| 53.22| 757| 2| 1020| 3| 1| 2| 1000| 1| 1020| 22821.5| HIGH| 0.0| | MI| Midwest| F| Truck Driver| No| 26| 1| 61012| 1| 1| 1| 1| MARRIED| 0| 2400| 1314| 53.27| 2299| 3| 1300| 3| 1| 1| 1100| 0| 1300| 21353.0| MEDIUM| 0.0| | CA| West| M| Author| No| 78| 1| 65134| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.08| 514| 4| 0| 1| 0| 0| 900| 0| 0| 20083.5| MEDIUM| 0.0| | CA| West| F| Not specified| No| 49| 1| 60322| 3| 1| 1| 4| WIDOWED| 30650| 9550| 266| 53.73| 33850| 12| 6500| 4| 1| 4| 900| 2| 1700| 20980.5| MEDIUM| 0.0| | MN| West| M| PROF-15| No| 80| 1| 63067| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 1| 1400| 0| 0|19766.75| MEDIUM| 0.0| | WA| West| F| PROF-39| No| 52| 1| 73090| 5| 1| 2| 5| DIVORCED| 0| 1750| 25| 53.33| 249| 10| 2728| 3| 2| 3| 1500| 1| 2728| 30972.5|VERY HIGH| 0.0| | NY|NorthEast| F| Publisher| No| 31| 0| 71782| 5| 1| 1| 5| DIVORCED| 0| 0| 34| 53.02| 0| 0| 2200| 2| 1| 1| 1500| 1| 2200| 23545.5| HIGH| 0.0| | NY|NorthEast| M| Childcare Worker| No| 44| 1| 61056| 1| 1| 1| 1| DIVORCED| 0| 0| 461| 53.15| 965| 0| 2200| 2| 1| 1| 1000| 0| 2200| 23164.0| HIGH| 0.0| | NM|Southwest| M| Cashier| No| 52| 0| 59480| 3| 1| 1| 4| MARRIED| 0| 0| 25| 53.06| 0| 0| 1001| 0| 1| 0| 700| 1| 1001| 26070.0| HIGH| 0.0| | MI| Midwest| M| Nurse| No| 77| 0| 73461| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 598| 0| 0| 0| 0| 0| 2500| 0| 0|27065.25| HIGH| 0.0| | CA| West| M|Administrative As...| No| 69| 1| 61943| 0| 1| 1| 2| MARRIED| 0| 0| 1466| 53.12| 1971| 0| 909| 2| 1| 1| 600| 0| 909|28385.75| HIGH| 0.0| | DC|NorthEast| M|Administrative As...| No| 46| 1| 64648| 0| 1| 1| 2| DIVORCED| 0| 0| 25| 53.06| 0| 0| 2500| 0| 1| 1| 800| 1| 2500| 26762.0| HIGH| 0.0| | MI| Midwest| M| Nurse| No| 42| 1| 58327| 0| 1| 0| 4| SINGLE| 0| 5100| 1926| 53.45| 2570| 4| 0| 4| 0| 2| 800| 1| 1500|15781.75| MEDIUM| 0.0| | OK| Midwest| M|Programmer/Developer| No| 28| 0| 60968| 2| 1| 1| 2| MARRIED| 0| 750| 206| 53.0| 725| 3| 700| 2| 1| 3| 900| 1| 700| 25042.0| HIGH| 0.0| +-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+ only showing top 20 rows
Aplicando o algoritmo de OneHot para variaveis categoricas:
In [ ]:
cat_cols = ['REGION', 'SEX', 'MARITAL_STATUS','LTV_BIN']
# Pipeline stages for string indexing and one-hot encoding
stages = []
for col in cat_cols:
stringIndexer = StringIndexer(inputCol=col, outputCol=col + "_index")
encoder = OneHotEncoder(inputCols=[stringIndexer.getOutputCol()], outputCols=[col + "_onehot"])
stages += [stringIndexer, encoder]
# Pipeline
pipeline = Pipeline(stages=stages)
# Fit the pipeline to the data
pipelineModel = pipeline.fit(df)
# Transform the data
transformed_df = pipelineModel.transform(df)
# Pivot the one-hot encoded columns
for col in cat_cols:
# Get distinct values in the column
distinct_values = transformed_df.select(col + "_index").distinct().rdd.flatMap(lambda x: x).collect()
for value in distinct_values:
# Pivot each distinct value into a new column
transformed_df = transformed_df.withColumn(col + "_" + str(int(value)) + "_onehot", (transformed_df[col + "_index"] == value).cast("int"))
# Drop the intermediate columns
transformed_df = transformed_df.drop(*[col + "_index" for col in cat_cols])
transformed_df = transformed_df.drop(*[col + "_onehot" for col in cat_cols])
# Show the transformed data
transformed_df.show()
+-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ |STATE| REGION|SEX| PROFESSION|BUY_INSURANCE|AGE|HAS_CHILDREN|SALARY|N_OF_DEPENDENTS|CAR_OWNERSHIP|HOUSE_OWNERSHIP|TIME_AS_CUSTOMER|MARITAL_STATUS|CREDIT_BALANCE|BANK_FUNDS|CHECKING_AMOUNT|MONEY_MONTLY_OVERDRAWN|T_AMOUNT_AUTOM_PAYMENTS|MONTHLY_CHECKS_WRITTEN|MORTGAGE_AMOUNT|N_TRANS_ATM|N_MORTGAGES|N_TRANS_TELLER|CREDIT_CARD_LIMITS|N_TRANS_KIOSK|N_TRANS_WEB_BANK| LTV| LTV_BIN|label|REGION_0_onehot|REGION_1_onehot|REGION_4_onehot|REGION_3_onehot|REGION_2_onehot|SEX_0_onehot|SEX_1_onehot|MARITAL_STATUS_0_onehot|MARITAL_STATUS_1_onehot|MARITAL_STATUS_4_onehot|MARITAL_STATUS_3_onehot|MARITAL_STATUS_2_onehot|LTV_BIN_0_onehot|LTV_BIN_1_onehot|LTV_BIN_3_onehot|LTV_BIN_2_onehot| +-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ | WI| Midwest| F| PROF-9| No| 49| 1| 68696| 1| 1| 1| 1| WIDOWED| 0| 16100| 25| 53.14| 1749| 4| 5500| 2| 1| 5| 800| 1| 3700| 25574.0| HIGH| 0.0| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| | CA| West| M| Nurse| No| 24| 0| 73850| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 0| 1500| 1| 0| 21862.5| MEDIUM| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | MI| Midwest| M|Programmer/Developer| No| 26| 1| 60249| 2| 1| 1| 2| MARRIED| 452| 500| 134| 53.42| 625| 4| 1036| 4| 1| 2| 1000| 4| 1036|19662.25| MEDIUM| 0.0| 0| 0| 0| 0| 1| 1| 0| 0| 1| 0| 0| 0| 0| 1| 0| 0| | NY|NorthEast| M|Programmer/Developer| No| 32| 0| 60466| 1| 1| 0| 1| SINGLE| 0| 650| 265| 53.18| 278| 17| 0| 3| 0| 2| 700| 3| 0| 16816.5| MEDIUM| 0.0| 1| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | NY|NorthEast| M|Construction Laborer| No| 24| 0| 76570| 3| 1| 1| 3| SINGLE| 0| 0| 25| 53.06| 0| 1| 358| 0| 1| 0| 1500| 1| 358| 27042.5| HIGH| 0.0| 1| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| | UT|Southwest| M| Truck Driver| No| 35| 1| 62756| 1| 1| 1| 1| MARRIED| 0| 501| 1382| 53.32| 1636| 8| 1500| 3| 1| 2| 500| 1| 1500| 22689.0| HIGH| 0.0| 0| 0| 1| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | NY|NorthEast| F| IT Staff| No| 36| 1| 62886| 1| 1| 1| 1| MARRIED| 0| 600| 25| 53.22| 757| 2| 1020| 3| 1| 2| 1000| 1| 1020| 22821.5| HIGH| 0.0| 1| 0| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 1| 0| 0| 0| | MI| Midwest| F| Truck Driver| No| 26| 1| 61012| 1| 1| 1| 1| MARRIED| 0| 2400| 1314| 53.27| 2299| 3| 1300| 3| 1| 1| 1100| 0| 1300| 21353.0| MEDIUM| 0.0| 0| 0| 0| 0| 1| 0| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| | CA| West| M| Author| No| 78| 1| 65134| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.08| 514| 4| 0| 1| 0| 0| 900| 0| 0| 20083.5| MEDIUM| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | CA| West| F| Not specified| No| 49| 1| 60322| 3| 1| 1| 4| WIDOWED| 30650| 9550| 266| 53.73| 33850| 12| 6500| 4| 1| 4| 900| 2| 1700| 20980.5| MEDIUM| 0.0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 1| 0| 0| 1| 0| 0| | MN| West| M| PROF-15| No| 80| 1| 63067| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 1| 1400| 0| 0|19766.75| MEDIUM| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | WA| West| F| PROF-39| No| 52| 1| 73090| 5| 1| 2| 5| DIVORCED| 0| 1750| 25| 53.33| 249| 10| 2728| 3| 2| 3| 1500| 1| 2728| 30972.5|VERY HIGH| 0.0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 1| | NY|NorthEast| F| Publisher| No| 31| 0| 71782| 5| 1| 1| 5| DIVORCED| 0| 0| 34| 53.02| 0| 0| 2200| 2| 1| 1| 1500| 1| 2200| 23545.5| HIGH| 0.0| 1| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 1| 1| 0| 0| 0| | NY|NorthEast| M| Childcare Worker| No| 44| 1| 61056| 1| 1| 1| 1| DIVORCED| 0| 0| 461| 53.15| 965| 0| 2200| 2| 1| 1| 1000| 0| 2200| 23164.0| HIGH| 0.0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 1| 1| 0| 0| 0| | NM|Southwest| M| Cashier| No| 52| 0| 59480| 3| 1| 1| 4| MARRIED| 0| 0| 25| 53.06| 0| 0| 1001| 0| 1| 0| 700| 1| 1001| 26070.0| HIGH| 0.0| 0| 0| 1| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | MI| Midwest| M| Nurse| No| 77| 0| 73461| 0| 1| 0| 2| SINGLE| 0| 0| 25| 53.06| 598| 0| 0| 0| 0| 0| 2500| 0| 0|27065.25| HIGH| 0.0| 0| 0| 0| 0| 1| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| | CA| West| M|Administrative As...| No| 69| 1| 61943| 0| 1| 1| 2| MARRIED| 0| 0| 1466| 53.12| 1971| 0| 909| 2| 1| 1| 600| 0| 909|28385.75| HIGH| 0.0| 0| 1| 0| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | DC|NorthEast| M|Administrative As...| No| 46| 1| 64648| 0| 1| 1| 2| DIVORCED| 0| 0| 25| 53.06| 0| 0| 2500| 0| 1| 1| 800| 1| 2500| 26762.0| HIGH| 0.0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 1| 1| 0| 0| 0| | MI| Midwest| M| Nurse| No| 42| 1| 58327| 0| 1| 0| 4| SINGLE| 0| 5100| 1926| 53.45| 2570| 4| 0| 4| 0| 2| 800| 1| 1500|15781.75| MEDIUM| 0.0| 0| 0| 0| 0| 1| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | OK| Midwest| M|Programmer/Developer| No| 28| 0| 60968| 2| 1| 1| 2| MARRIED| 0| 750| 206| 53.0| 725| 3| 700| 2| 1| 3| 900| 1| 700| 25042.0| HIGH| 0.0| 0| 0| 0| 0| 1| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| +-----+---------+---+--------------------+-------------+---+------------+------+---------------+-------------+---------------+----------------+--------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+---------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ only showing top 20 rows
Removendo mais algumas colunas:
In [ ]:
columns_to_drop = ['STATE','REGION','SEX','PROFESSION','MARITAL_STATUS','LTV_BIN','BUY_INSURANCE']
transformed_df = transformed_df.drop(*columns_to_drop)
transformed_df.show()
+---+------------+------+---------------+-------------+---------------+----------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ |AGE|HAS_CHILDREN|SALARY|N_OF_DEPENDENTS|CAR_OWNERSHIP|HOUSE_OWNERSHIP|TIME_AS_CUSTOMER|CREDIT_BALANCE|BANK_FUNDS|CHECKING_AMOUNT|MONEY_MONTLY_OVERDRAWN|T_AMOUNT_AUTOM_PAYMENTS|MONTHLY_CHECKS_WRITTEN|MORTGAGE_AMOUNT|N_TRANS_ATM|N_MORTGAGES|N_TRANS_TELLER|CREDIT_CARD_LIMITS|N_TRANS_KIOSK|N_TRANS_WEB_BANK| LTV|label|REGION_0_onehot|REGION_1_onehot|REGION_4_onehot|REGION_3_onehot|REGION_2_onehot|SEX_0_onehot|SEX_1_onehot|MARITAL_STATUS_0_onehot|MARITAL_STATUS_1_onehot|MARITAL_STATUS_4_onehot|MARITAL_STATUS_3_onehot|MARITAL_STATUS_2_onehot|LTV_BIN_0_onehot|LTV_BIN_1_onehot|LTV_BIN_3_onehot|LTV_BIN_2_onehot| +---+------------+------+---------------+-------------+---------------+----------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ | 49| 1| 68696| 1| 1| 1| 1| 0| 16100| 25| 53.14| 1749| 4| 5500| 2| 1| 5| 800| 1| 3700| 25574.0| 0.0| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| | 24| 0| 73850| 0| 1| 0| 2| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 0| 1500| 1| 0| 21862.5| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | 26| 1| 60249| 2| 1| 1| 2| 452| 500| 134| 53.42| 625| 4| 1036| 4| 1| 2| 1000| 4| 1036|19662.25| 0.0| 0| 0| 0| 0| 1| 1| 0| 0| 1| 0| 0| 0| 0| 1| 0| 0| | 32| 0| 60466| 1| 1| 0| 1| 0| 650| 265| 53.18| 278| 17| 0| 3| 0| 2| 700| 3| 0| 16816.5| 0.0| 1| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | 24| 0| 76570| 3| 1| 1| 3| 0| 0| 25| 53.06| 0| 1| 358| 0| 1| 0| 1500| 1| 358| 27042.5| 0.0| 1| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| | 35| 1| 62756| 1| 1| 1| 1| 0| 501| 1382| 53.32| 1636| 8| 1500| 3| 1| 2| 500| 1| 1500| 22689.0| 0.0| 0| 0| 1| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | 36| 1| 62886| 1| 1| 1| 1| 0| 600| 25| 53.22| 757| 2| 1020| 3| 1| 2| 1000| 1| 1020| 22821.5| 0.0| 1| 0| 0| 0| 0| 0| 1| 0| 1| 0| 0| 0| 1| 0| 0| 0| | 26| 1| 61012| 1| 1| 1| 1| 0| 2400| 1314| 53.27| 2299| 3| 1300| 3| 1| 1| 1100| 0| 1300| 21353.0| 0.0| 0| 0| 0| 0| 1| 0| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| | 78| 1| 65134| 0| 1| 0| 2| 0| 0| 25| 53.08| 514| 4| 0| 1| 0| 0| 900| 0| 0| 20083.5| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | 49| 1| 60322| 3| 1| 1| 4| 30650| 9550| 266| 53.73| 33850| 12| 6500| 4| 1| 4| 900| 2| 1700| 20980.5| 0.0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 1| 0| 0| 1| 0| 0| | 80| 1| 63067| 0| 1| 0| 2| 0| 0| 25| 53.06| 504| 0| 0| 0| 0| 1| 1400| 0| 0|19766.75| 0.0| 0| 1| 0| 0| 0| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | 52| 1| 73090| 5| 1| 2| 5| 0| 1750| 25| 53.33| 249| 10| 2728| 3| 2| 3| 1500| 1| 2728| 30972.5| 0.0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 1| | 31| 0| 71782| 5| 1| 1| 5| 0| 0| 34| 53.02| 0| 0| 2200| 2| 1| 1| 1500| 1| 2200| 23545.5| 0.0| 1| 0| 0| 0| 0| 0| 1| 0| 0| 0| 0| 1| 1| 0| 0| 0| | 44| 1| 61056| 1| 1| 1| 1| 0| 0| 461| 53.15| 965| 0| 2200| 2| 1| 1| 1000| 0| 2200| 23164.0| 0.0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 1| 1| 0| 0| 0| | 52| 0| 59480| 3| 1| 1| 4| 0| 0| 25| 53.06| 0| 0| 1001| 0| 1| 0| 700| 1| 1001| 26070.0| 0.0| 0| 0| 1| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | 77| 0| 73461| 0| 1| 0| 2| 0| 0| 25| 53.06| 598| 0| 0| 0| 0| 0| 2500| 0| 0|27065.25| 0.0| 0| 0| 0| 0| 1| 1| 0| 1| 0| 0| 0| 0| 1| 0| 0| 0| | 69| 1| 61943| 0| 1| 1| 2| 0| 0| 1466| 53.12| 1971| 0| 909| 2| 1| 1| 600| 0| 909|28385.75| 0.0| 0| 1| 0| 0| 0| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| | 46| 1| 64648| 0| 1| 1| 2| 0| 0| 25| 53.06| 0| 0| 2500| 0| 1| 1| 800| 1| 2500| 26762.0| 0.0| 1| 0| 0| 0| 0| 1| 0| 0| 0| 0| 0| 1| 1| 0| 0| 0| | 42| 1| 58327| 0| 1| 0| 4| 0| 5100| 1926| 53.45| 2570| 4| 0| 4| 0| 2| 800| 1| 1500|15781.75| 0.0| 0| 0| 0| 0| 1| 1| 0| 1| 0| 0| 0| 0| 0| 1| 0| 0| | 28| 0| 60968| 2| 1| 1| 2| 0| 750| 206| 53.0| 725| 3| 700| 2| 1| 3| 900| 1| 700| 25042.0| 0.0| 0| 0| 0| 0| 1| 1| 0| 0| 1| 0| 0| 0| 1| 0| 0| 0| +---+------------+------+---------------+-------------+---------------+----------------+--------------+----------+---------------+----------------------+-----------------------+----------------------+---------------+-----------+-----------+--------------+------------------+-------------+----------------+--------+-----+---------------+---------------+---------------+---------------+---------------+------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------------+----------------+----------------+----------------+ only showing top 20 rows
Fazendo o Vector Assembler dos dados:
In [ ]:
numericCols = ['AGE', 'HAS_CHILDREN', 'SALARY', 'N_OF_DEPENDENTS', 'CAR_OWNERSHIP', 'HOUSE_OWNERSHIP', 'TIME_AS_CUSTOMER', 'CREDIT_BALANCE', 'BANK_FUNDS', 'CHECKING_AMOUNT', 'MONEY_MONTLY_OVERDRAWN', 'T_AMOUNT_AUTOM_PAYMENTS', 'MONTHLY_CHECKS_WRITTEN', 'MORTGAGE_AMOUNT', 'N_TRANS_ATM', 'N_MORTGAGES', 'N_TRANS_TELLER', 'CREDIT_CARD_LIMITS', 'N_TRANS_KIOSK', 'N_TRANS_WEB_BANK', 'LTV', 'REGION_0_onehot', 'REGION_1_onehot', 'REGION_4_onehot', 'REGION_3_onehot', 'REGION_2_onehot', 'SEX_0_onehot', 'SEX_1_onehot', 'MARITAL_STATUS_0_onehot', 'MARITAL_STATUS_1_onehot', 'MARITAL_STATUS_4_onehot', 'MARITAL_STATUS_3_onehot', 'MARITAL_STATUS_2_onehot', 'LTV_BIN_0_onehot', 'LTV_BIN_1_onehot', 'LTV_BIN_3_onehot', 'LTV_BIN_2_onehot']
for col_name in numericCols:
transformed_df = transformed_df.withColumn(col_name, col(col_name).cast("float"))
assembler = VectorAssembler(inputCols=numericCols, outputCol="features")
transformed_df_final = assembler.transform(transformed_df)
transformed_df_final = transformed_df_final.select(["label", "features"])
transformed_df_final.show()
+-----+--------------------+ |label| features| +-----+--------------------+ | 0.0|[49.0,1.0,68696.0...| | 0.0|(37,[0,2,4,6,9,10...| | 0.0|[26.0,1.0,60249.0...| | 0.0|(37,[0,2,3,4,6,8,...| | 0.0|(37,[0,2,3,4,5,6,...| | 0.0|[35.0,1.0,62756.0...| | 0.0|[36.0,1.0,62886.0...| | 0.0|(37,[0,1,2,3,4,5,...| | 0.0|(37,[0,1,2,4,6,9,...| | 0.0|[49.0,1.0,60322.0...| | 0.0|(37,[0,1,2,4,6,9,...| | 0.0|[52.0,1.0,73090.0...| | 0.0|(37,[0,2,3,4,5,6,...| | 0.0|(37,[0,1,2,3,4,5,...| | 0.0|(37,[0,2,3,4,5,6,...| | 0.0|(37,[0,2,4,6,9,10...| | 0.0|(37,[0,1,2,4,5,6,...| | 0.0|(37,[0,1,2,4,5,6,...| | 0.0|(37,[0,1,2,4,6,8,...| | 0.0|(37,[0,2,3,4,5,6,...| +-----+--------------------+ only showing top 20 rows
Modelo de DecisionTree¶
Execução do algoritmo¶
In [ ]:
from pyspark.ml.classification import DecisionTreeClassifier
from pyspark.ml.tuning import ParamGridBuilder, CrossValidator
from pyspark.ml.evaluation import BinaryClassificationEvaluator
from pyspark.mllib.evaluation import BinaryClassificationMetrics
train, test = transformed_df_final.randomSplit([0.7, 0.3], seed = 2018)
rf_m = DecisionTreeClassifier(labelCol = 'label' , featuresCol = 'features')
dtparamGrid = (ParamGridBuilder()
.addGrid(rf_m.maxDepth, [2, 5, 10, 20, 30])
.addGrid(rf_m.maxBins, [10, 20, 40, 80, 100])
.build())
dtevaluator = BinaryClassificationEvaluator(rawPredictionCol="rawPrediction")
dtcv = CrossValidator(estimator = rf_m,
estimatorParamMaps = dtparamGrid,
evaluator = dtevaluator,
numFolds = 5)
dtcvModel = dtcv.fit(train)
dtpredictions = dtcvModel.transform(test)
Resultados Numéricos¶
In [ ]:
from pyspark.ml.evaluation import MulticlassClassificationEvaluator
eval_accuracy = MulticlassClassificationEvaluator(labelCol="label", predictionCol="prediction", metricName="accuracy")
eval_precision = MulticlassClassificationEvaluator(labelCol="label", predictionCol="prediction", metricName="precisionByLabel")
eval_recall = MulticlassClassificationEvaluator(labelCol="label", predictionCol="prediction", metricName="recallByLabel")
eval_f1 = MulticlassClassificationEvaluator(labelCol="label", predictionCol="prediction", metricName="f1")
accuracy = eval_accuracy.evaluate(dtpredictions)
precision = eval_precision.evaluate(dtpredictions)
recall = eval_recall.evaluate(dtpredictions)
f1score = eval_f1.evaluate(dtpredictions)
# Evaluate best model
print('Accuracy:', format(accuracy, ".2%"))
print('Precision:', format(precision, ".2%"))
print('Recall:', format(recall, ".2%"))
print('F1score:', format(f1score, ".2%"))
print('AUC:', format(BinaryClassificationMetrics(dtpredictions['label','prediction'].rdd).areaUnderROC, ".2%"))
Accuracy: 84.35% Precision: 90.34% Recall: 87.79% F1score: 84.52% AUC: 80.23%
Gráficos¶
In [ ]:
regras = dtcvModel.bestModel.toDebugString.replace("Predict: 1.0", "BUY_INSURANCE: Yes").replace("Predict: 0.0", "BUY_INSURANCE: No")
f = 0
used_features = []
for feature in numericCols:
regras = regras.replace(f"feature {f} ", f"{feature} ")
if feature in regras:
used_features.append(feature)
f = f +1
print(regras)
DecisionTreeClassificationModel: uid=DecisionTreeClassifier_cb83e7aa706b, depth=5, numNodes=35, numClasses=2, numFeatures=37
If (BANK_FUNDS <= 270.5)
If (BANK_FUNDS <= 225.5)
If (CREDIT_CARD_LIMITS <= 650.0)
If (SALARY <= 66221.5)
BUY_INSURANCE: No
Else (SALARY > 66221.5)
If (AGE <= 32.5)
BUY_INSURANCE: Yes
Else (AGE > 32.5)
BUY_INSURANCE: No
Else (CREDIT_CARD_LIMITS > 650.0)
BUY_INSURANCE: No
Else (BANK_FUNDS > 225.5)
If (MONEY_MONTLY_OVERDRAWN <= 53.60499954223633)
If (N_TRANS_TELLER <= 1.5)
BUY_INSURANCE: Yes
Else (N_TRANS_TELLER > 1.5)
BUY_INSURANCE: No
Else (MONEY_MONTLY_OVERDRAWN > 53.60499954223633)
BUY_INSURANCE: Yes
Else (BANK_FUNDS > 270.5)
If (CHECKING_AMOUNT <= 68.0)
If (CREDIT_BALANCE <= 999.0)
If (MONEY_MONTLY_OVERDRAWN <= 53.26499938964844)
If (T_AMOUNT_AUTOM_PAYMENTS <= 704.5)
BUY_INSURANCE: Yes
Else (T_AMOUNT_AUTOM_PAYMENTS > 704.5)
BUY_INSURANCE: No
Else (MONEY_MONTLY_OVERDRAWN > 53.26499938964844)
BUY_INSURANCE: Yes
Else (CREDIT_BALANCE > 999.0)
If (SALARY <= 75522.5)
BUY_INSURANCE: No
Else (SALARY > 75522.5)
If (AGE <= 50.5)
BUY_INSURANCE: Yes
Else (AGE > 50.5)
BUY_INSURANCE: No
Else (CHECKING_AMOUNT > 68.0)
If (MONEY_MONTLY_OVERDRAWN <= 54.2549991607666)
BUY_INSURANCE: No
Else (MONEY_MONTLY_OVERDRAWN > 54.2549991607666)
If (CHECKING_AMOUNT <= 1936.0)
If (LTV <= 32137.0)
BUY_INSURANCE: Yes
Else (LTV > 32137.0)
BUY_INSURANCE: No
Else (CHECKING_AMOUNT > 1936.0)
If (LTV <= 20652.375)
BUY_INSURANCE: Yes
Else (LTV > 20652.375)
BUY_INSURANCE: No
In [ ]:
def parse_debug_string_lines(lines):
block = []
while lines:
if lines[0].startswith('If'):
bl = ' '.join(lines.pop(0).split()[1:]).replace('(', '').replace(')', '')
block.append({'name': bl, 'children': parse_debug_string_lines(lines)})
if lines[0].startswith('Else'):
be = ' '.join(lines.pop(0).split()[1:]).replace('(', '').replace(')', '')
block.append({'name': be, 'children': parse_debug_string_lines(lines)})
elif not lines[0].startswith(('If', 'Else')):
block2 = lines.pop(0)
block.append({'name': block2})
else:
break
return block
def debug_str_to_json(debug_string):
data = []
for line in debug_string.splitlines():
if line.strip():
line = line.strip()
data.append(line)
else:
break
if not line: break
json = {'name': 'Root', 'children': parse_debug_string_lines(data[1:])}
return json
import json
dict_tree_json = debug_str_to_json(regras)
# print(json.dumps(dict_tree_json,indent = 1 ))
Jogando o json no site https://jsoncrack.com/editor
In [ ]:
from IPython.display import SVG, display
def show_svg():
display(SVG(filename='/content/jsoncrack.com.svg'))
show_svg()
Análise e Explicação¶
Métricas de Desempenho¶
- Acurácia de 84,35% sugere que a maioria das previsões do modelo corresponde ao resultado real.
- Precisão de 90,34% indica que, quando o modelo prevê que um cliente comprará seguro, é correto na maioria das vezes.
- Recall de 87,79% mostra que o modelo identificou com sucesso a maioria dos clientes reais que comprariam seguros.
- F1score de 84,52% reflete um equilíbrio saudável entre precisão e recall, o que é crucial em contextos onde ambos são importantes.
- AUC de 80,23% demonstra uma boa capacidade do modelo de diferenciar entre os que comprariam e os que não comprariam seguros.
Importância das Características¶
O gráfico de importância das características mostra quais variáveis mais influenciam a decisão do modelo. Variáveis no topo do gráfico são as que mais impactam na decisão do modelo, sugerindo que elas têm um papel crucial em prever se um cliente vai comprar seguro imobiliário ou não.
Análise dos Resultados¶
A análise indica que algumas características são particularmente influentes na previsão de compra de seguro. Com base nas posições mais altas no gráfico, podemos inferir que variáveis como número de transações no ATM, montante mensalmente excedido e possivelmente outras relacionadas ao comportamento bancário e financeiro do cliente são determinantes.
Distribuição dos Dados dos Clientes:¶
- HAS_CHILDREN: Aproximadamente a mesma quantidade de clientes tem e não tem filhos.
- MARITAL_STATUS: Mais clientes são casados, seguidos por solteiros e divorciados.
- REGION: A maioria dos clientes está no Oeste, seguida pelo Midwest.
- SEX: Mais clientes são do sexo masculino.
- PROFESSION: Há uma variedade de profissões, com destaque para administradores e enfermeiros.
- LTV_BIN: A maioria dos clientes tem um valor de "LTV" classificado como médio ou alto.
Correlação de Compra de Seguro:¶
O mapa de calor mostra BUY_INSURANCE altamente correlacionado com vários fatores financeiros como BANK_FUNDS, CHECKING_AMOUNT e CREDIT_BALANCE.
Árvore de Decisão:¶
A árvore de decisão ilustra a lógica de previsão de quem compra seguro imobiliário. Fatores como BANK_FUNDS, CHECKING_AMOUNT, e CREDIT_CARD_LIMITS são decisivos.
Perfil Encontrado¶
Com base nos dados e na árvore de decisão, clientes com alta probabilidade de compra de seguro imobiliário tendem a ter:
- Fundos bancários superiores a 270,5.
- Quantidade de transações com caixas eletrônicos abaixo de 1,5.
- Uma quantidade de dinheiro regularmente excedida inferior a 53,6.
- Limites de cartão de crédito abaixo de 650.
- Salários acima de 66.221,5 ou abaixo, mas com idade inferior a 32,5 anos.
Estes insights sugerem que indivíduos com uma sólida saúde financeira e comportamentos de gasto conservadores têm mais probabilidade de adquirir seguro imobiliário. As informações também indicam que variáveis financeiras são mais decisivas do que demográficas (como idade e estado civil) para a previsão da compra de seguro.